﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml.Linq;
using System.Xml;
using System.Web.Script.Serialization;
using System.Collections;

namespace Twi.COMMON.Core
{
    /*
     * CopyRight:©2013 踏影软件开发工作室（Tyingsoft/Twilight）版权所有
     * Author:xuzhihong
     * Create Date: 2013-08-16
     * Description: DataTable辅助类
     */
    public class TwiDataTableHelper
    {
        /// <summary>
        /// 获取DataTable指定列名去掉重复列的值集合
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="colName"></param>
        /// <returns></returns>
        public static List<dynamic> GetDistinctColValues(DataTable dt,string colName)
        {
            List<dynamic> list = new List<dynamic>();
            var q = (from p in dt.AsEnumerable()
                    select p[colName]).Distinct();
            return q.ToList();
        }

        /// <summary>
        /// 获取DataTable指定列的最大值
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="colName"></param>
        /// <returns></returns>
        public static dynamic GetMaxColValue(DataTable dt, string colName)
        {
            var q = (from p in dt.AsEnumerable()
                     select p[colName]).Max();
            return q;
        }

        /// <summary>
        /// 获取DataRow[]数组指定列求和的值
        /// </summary>
        /// <param name="row"></param>
        /// <param name="colName"></param>
        /// <returns></returns>
        public static dynamic GetSumColValue(DataRow[] rows, string colName)
        {
            if (rows.Length == 0)
            {
                return "";
            }
            dynamic sum = 0;
            dynamic dy = rows[0][colName];
            if (dy is int)
            {
                foreach (DataRow dr in rows)
                {
                    sum += dr[colName] is DBNull ? 0 : (int)dr[colName];
                }
            }
            else if (dy is decimal)
            {
                foreach (DataRow dr in rows)
                {
                    sum += dr[colName] is DBNull ? 0 : (decimal)dr[colName];
                }
            }
            else if (dy is double)
            {
                foreach (DataRow dr in rows)
                {
                    sum += dr[colName] is DBNull ? 0 : (double)dr[colName];
                }
            }
            else if (dy is float)
            {
                foreach (DataRow dr in rows)
                {
                    sum += dr[colName] is DBNull ? 0 : (float)dr[colName];
                }
            }
            return sum;
        }

        /// <summary>
        /// 获取DataTable指定列求和的值
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="colName"></param>
        /// <returns></returns>
        public static dynamic GetSumColValue(DataTable dt, string colName)
        {
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            dynamic sum = 0;
            var rows = dt.Rows;
            dynamic dy = rows[0][colName];
            if (dy is int)
            {
                foreach (DataRow dr in rows)
                {
                    sum += dr[colName] is DBNull ? 0 :(int)dr[colName];
                }
            }
            else if (dy is decimal)
            {
                foreach (DataRow dr in rows)
                {
                    sum += dr[colName] is DBNull ? 0 : (decimal)dr[colName];                   
                }
            }
            else if (dy is double)
            {
                foreach (DataRow dr in rows)
                {
                    sum += dr[colName] is DBNull ? 0 : (double)dr[colName];
                }
            }
            else if (dy is float)
            {
                foreach (DataRow dr in rows)
                {
                    sum += dr[colName] is DBNull ? 0 : (float)dr[colName];
                }
            }

            return sum;
        }


        /// <summary>
        /// 转化DataTable列名为大写
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static DataTable ColNameToUpper(DataTable dt)
        {
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.ToUpper();
            }
            return dt;
        }

        /// <summary>
        /// 将DataTable转换为XElement对象
        /// </summary>
        /// <param name="dt">要转换的DataTable</param>
        /// <param name="nodeName">XElement根节点名称 默认为：Item</param>
        /// <param name="nodeSuffix">XElement根节点后缀  默认为：List</param>
        /// <returns></returns>
        public static XElement DataTableToXElement(DataTable dt, string nodeName = "Item", string nodeSuffix="List")
        {
            XElement xele = new XElement(nodeName + nodeSuffix);
            XElement x = null;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                x = new XElement(nodeName);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    XElement xel = new XElement(dt.Columns[j].ColumnName, dt.Rows[i][j]);
                    x.Add(xel);
                }
                xele.Add(x);
            }
            return xele;
        }

        /// <summary>
        /// 将DataTable转化为XmlDocument对象
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private XmlDocument DataTableToXmlDocument(DataTable dt)
        {
            XmlDocument xml = new XmlDocument();
            StringBuilder sb = new StringBuilder();

            sb.Append("<?xml version='1.0' encoding='utf-8' ?>");
            sb.Append("<DocumentElement>");

            foreach (DataRow dr in dt.Rows)
            {
                sb.Append("<" + dt.TableName + ">");

                foreach (DataColumn dc in dt.Columns)
                    sb.Append("<" + dc.ColumnName + ">" +
                            dr[dc].ToString() +
                         "</" + dc.ColumnName + ">");

                sb.Append("</" + dt.TableName + ">");
            }

            sb.Append("</DocumentElement>");
            xml.LoadXml(sb.ToString());

            return xml;
        }


        /// <summary>
        /// JSON字符串转成DataTable
        /// </summary>
        /// <param name="json"></param>
        /// <returns></returns>
        public static DataTable JSONToDataTable(string json)
        {
            DataTable dataTable = new DataTable();  //实例化
            DataTable result;
            try
            {
                JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
                javaScriptSerializer.MaxJsonLength = Int32.MaxValue; //取得最大数值
                ArrayList arrayList = javaScriptSerializer.Deserialize<ArrayList>(json);
                if (arrayList.Count > 0)
                {
                    foreach (Dictionary<string, object> dictionary in arrayList)
                    {
                        if (dictionary.Keys.Count<string>() == 0)
                        {
                            result = dataTable;
                            return result;
                        }
                        if (dataTable.Columns.Count == 0)
                        {
                            foreach (string current in dictionary.Keys)
                            {
                                dataTable.Columns.Add(current, dictionary[current].GetType());
                            }
                        }
                        DataRow dataRow = dataTable.NewRow();
                        foreach (string current in dictionary.Keys)
                        {
                            dataRow[current] = dictionary[current];
                        }

                        dataTable.Rows.Add(dataRow); //循环添加行到DataTable中
                    }
                }
            }
            catch
            {
            }
            result = dataTable;
            return result;
        }

        /// <summary>
        /// 将DataTable转化为Json字符串
        /// </summary>
        /// <param name="dt"></param>
        /// <remarks></remarks>
        /// <returns></returns>
        public static string ToJson(DataTable dt, bool needAppendInfo = true, string totalCountField = "totalCount", string dataField = "data")
        {
            List<Dictionary<string, object>> list = ToListDict(dt);
            int count = 0;
            try
            {
                count = Convert.ToInt32(dt.TableName);
            }
            catch
            {
                count = dt.Rows.Count;
            }
            string strReturn = "";
            if (count == 0)
            {
                strReturn = needAppendInfo ? "{\"" + totalCountField + "\":0,\"" + dataField + "\":[]}" : "[]";
            }
            else
            {
                strReturn = TwiJsonHelper.ListToJson(list, count, needAppendInfo, totalCountField, dataField);
            }
            return strReturn;
        }

        /// <summary>
        /// 把DataTable转换为TwiReturn
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="twi"></param>
        /// <returns></returns>
        public static void ToTwiReturn(DataTable dt, ref TwiReturn twi)
        {
            twi.data = ToListDict(dt);
            try
            {
                twi.totalCount = Convert.ToInt32(dt.TableName);
            }
            catch
            {
                twi.totalCount = dt.Rows.Count;
            }
        }

        /// <summary>
        /// 把DataTable转换为TwiReturn序列化后的字符串
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="twi"></param>
        /// <returns></returns>
        public static string ToTwiJson(DataTable dt, ref TwiReturn twi) 
        {
            ToTwiReturn(dt, ref twi);
            string strReturn = JSSerialize.Serialize(twi);
            return strReturn;
        }

        /// <summary>
        /// DataTable转换为List字典
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<Dictionary<string, object>> ToListDict(DataTable dt)
        {
            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
            foreach (DataRow dr in dt.Rows)
            {
                Dictionary<string, object> result = new Dictionary<string, object>();
                foreach (DataColumn dc in dt.Columns)
                {
                    result.Add(dc.ColumnName, dr[dc].ToString());
                }
                list.Add(result);
            }
            return list;
        }
    }

    /// <summary>
    /// 之前helper单词少写了l，为了纠正，那直接继承了
    /// </summary>
    [Obsolete("之前helper单词少写了l,2015-04-30开始废弃，请使用正确的TwiDataTableHeper")]
    public class TwiDataTableHeper : TwiDataTableHelper
    { }
}
